import pandas as pd
import openpyxl
import re
import os
from datetime import datetime, timedelta
from difflib import get_close_matches
import xlwings as xw

# GROUP_NAME = {"英语群"}   # 例：微信群名称
GROUP_NAME = {"数学群", "语文群", "英语群"}   # 例：微信群名称
# GROUP_NAME = {"语文群"}   # 例：微信群名称
SAVE_DIR = ".\\WeChatTextExport"  # 导出目录，可自行修改

class Updater:
    def __init__(self, csv_file: str, old_excel_file: str, new_excel_file: str):
        self.csv_file = csv_file
        self.old_excel_file = old_excel_file
        self.new_excel_file = new_excel_file

        # 检查输入文件是否存在
        missing_files = []
        if not os.path.exists(csv_file):
            missing_files.append(csv_file)
        if not os.path.exists(old_excel_file):
            missing_files.append(old_excel_file)

        if missing_files:
            print("❌ 以下输入文件不存在：")
            for f in missing_files:
                print("   -", f)
            print("请检查文件路径或日期是否正确。")
            raise Exception("文件不存在")

    def recalc_and_save_excel(self):
        filepath = self.old_excel_file
        print(f"📘 打开 Excel 文件计算公式：{filepath}")

        app = xw.App(visible=False)  # 不显示 Excel 窗口
        try:
            wb = app.books.open(filepath)
            wb.app.calculate()        # 强制计算所有公式
            wb.save()
            print("✅ Excel 已重新计算并保存。")
        finally:
            wb.close()
            app.quit()

    def ask_overwrite(self):
        if os.path.exists(self.new_excel_file):
            resp = input(f"⚠️ 文件 {self.new_excel_file} 已存在，是否覆盖？(y/n)：").strip().lower()
            if resp != "y":
                return False
        return True
    
    def load_csv_safely(self, columns):
        # 先试 utf-8-sig（可自动去掉BOM），不行再退GBK
        for enc in ("utf-8-sig", "utf-8", "gbk"):
            try:
                df = pd.read_csv(self.csv_file, encoding=enc)
                break
            except Exception:
                df = None
        if df is None:
            raise RuntimeError("无法读取CSV，请检查编码。")

        # 标准化列名：去BOM、去空格（含全角）、去制表符
        def norm(s: str) -> str:
            s = str(s).replace("\ufeff", "")  # 去BOM
            s = s.replace("\u3000", " ")      # 全角空格→半角
            s = re.sub(r"\s+", "", s)         # 去所有空白
            return s

        # 期望列名（标准化前）
        expected_names = columns

        # 构建标准化映射：规范名 -> 原始名
        cols_by_norm = {norm(c): c for c in df.columns}

        # 尝试把期望列名映射到实际列名（容错：空白/轻微差异）
        resolved = {}
        for need in expected_names:
            key = norm(need)
            if key in cols_by_norm:
                resolved[need] = cols_by_norm[key]
            else:
                # 允许轻微差异，用模糊匹配兜底（比如多了/少了一个字、斜杠）
                candidates = get_close_matches(key, cols_by_norm.keys(), n=1, cutoff=0.75)
                if candidates:
                    resolved[need] = cols_by_norm[candidates[0]]

        # 检查缺失
        missing = [c for c in expected_names if c not in resolved]
        if missing:
            raise KeyError(
                "CSV缺少这些列（或列名有差异）："
                + "、".join(missing)
                + f"\n实际列名：{list(df.columns)}"
            )

        # 用解析到的真实列名重命名，后续代码就用“标准名”访问
        df = df.rename(columns={v: k for k, v in resolved.items()})
        return df

    def update_excel_with_target_data(self):
        pass

class EnglishUpdater(Updater):
    def __init__(self, name="英语群", csv_file: str = None, old_excel_file: str = None, new_excel_file: str = None):
        self.name = name
        # 期望列名（标准化前）
        self.csv_file_titles = [
            "学号",
            "当天单词词组默写次数",
            "当天课文背诵次数",
            "当天语法知识点学习次数",
        ]
        super().__init__(csv_file, old_excel_file, new_excel_file)

    def load_csv_safely(self):
        return super().load_csv_safely(self.csv_file_titles)

    def update_excel_with_target_data(self):
        # 读取 CSV 文件（学号与打卡次数数据）
        df_csv = self.load_csv_safely()
        if df_csv.empty:
            print(f"⚠️ CSV 文件为空：{self.csv_file}")
            return None

        # 用 data_only=True 打开“值视图”，读取 BCDE 的已计算数值
        wb_vals = openpyxl.load_workbook(self.old_excel_file, data_only=True, read_only=True)
        sh_vals = wb_vals.active

        # 读取 Excel 数据
        wb = openpyxl.load_workbook(self.old_excel_file)
        sheet = wb.active
        
        if sheet.max_row < 2:
            print(f"⚠️ Excel 文件中没有学生数据：{self.old_excel_file}")
            return None

        # 获取昨天的 Excel 数据（这里假设昨天的数据已经放置到 CDE 列）
        yesterday_data = {}
        for row in range(1, sh_vals.max_row + 1):
            student_id = sh_vals.cell(row=row, column=1).value
            if not student_id:
                continue
            student_id_str = str(student_id).strip()  # 去掉空格
            if not student_id_str.isdigit():
                continue

            yesterday_data[student_id] = {
                'B': sh_vals.cell(row=row, column=2).value,
                'C': sh_vals.cell(row=row, column=3).value,
                'D': sh_vals.cell(row=row, column=4).value,
                'E': sh_vals.cell(row=row, column=5).value,
            }
        
        # 获取 CSV 文件中当天的数据
        target_date_data = {}
        for _, row in df_csv.iterrows():
            sid = str(row[self.csv_file_titles[0]]).strip()
            if not sid.isdigit():
                continue
            target_date_data[int(sid)] = {
                "B": row[self.csv_file_titles[1]],
                "C": row[self.csv_file_titles[2]],
                "D": row[self.csv_file_titles[3]],
            }
        
        # 更新 Excel 文件
        for row in range(1, sheet.max_row + 1):
            student_id = sheet.cell(row=row, column=1).value
        
            if student_id in yesterday_data:
                # 将前一天的数据平移到 IJK 列
                sheet.cell(row=row, column=9, value=yesterday_data[student_id]['B'])
                sheet.cell(row=row, column=10, value=yesterday_data[student_id]['C'])
                sheet.cell(row=row, column=11, value=yesterday_data[student_id]['D'])
                sheet.cell(row=row, column=12, value=yesterday_data[student_id]['E'])
            
            if student_id in target_date_data:
                # 更新当天的数据到 FGH 列
                sheet.cell(row=row, column=6, value=target_date_data[student_id]['B'])
                sheet.cell(row=row, column=7, value=target_date_data[student_id]['C'])
                sheet.cell(row=row, column=8, value=target_date_data[student_id]['D'])

        # 保存新的 Excel 文件，命名为当天的日期（mmdd格式）
        new_file_path = None
        try:
            wb.save(self.new_excel_file)
            new_file_path = self.new_excel_file
        except PermissionError:
            print(f"❌ 无法保存文件：{self.new_excel_file}\n可能是该文件正在被 Excel 打开，请关闭后重试。")
        
        wb.close()
        wb_vals.close()
        return new_file_path

class MathsUpdater(Updater):
    def __init__(self, name="数学群", csv_file: str = None, old_excel_file: str = None, new_excel_file: str = None):
        self.name = name
        # 期望列名（标准化前）
        self.csv_file_titles = [
            "学号",
            f"当天{self.name[0:2]}打卡"
        ]
        super().__init__(csv_file, old_excel_file, new_excel_file)

    def load_csv_safely(self):
        return super().load_csv_safely(self.csv_file_titles)

    def update_excel_with_target_data(self):
        # 读取 CSV 文件（学号与打卡次数数据）
        df_csv = self.load_csv_safely()
        if df_csv.empty:
            print(f"⚠️ CSV 文件为空：{self.csv_file}")
            return None

        # 用 data_only=True 打开“值视图”，读取 BCDE 的已计算数值
        wb_vals = openpyxl.load_workbook(self.old_excel_file, data_only=True, read_only=True)
        sh_vals = wb_vals.active

        # 读取 Excel 数据
        wb = openpyxl.load_workbook(self.old_excel_file)
        sheet = wb.active
        
        if sheet.max_row < 2:
            print(f"⚠️ Excel 文件中没有学生数据：{self.old_excel_file}")
            return None

        # 获取昨天的 Excel 数据（这里假设昨天的数据已经放置到 CDE 列）
        yesterday_data = {}
        for row in range(1, sh_vals.max_row + 1):
            student_id = sh_vals.cell(row=row, column=1).value
            if not student_id:
                continue
            student_id_str = str(student_id).strip()  # 去掉空格
            if not student_id_str.isdigit():
                continue

            yesterday_data[student_id] = {
                'B': sh_vals.cell(row=row, column=2).value,
               }
        
        # 获取 CSV 文件中当天的数据
        target_date_data = {}
        for _, row in df_csv.iterrows():
            sid = str(row[self.csv_file_titles[0]]).strip()
            if not sid.isdigit():
                continue
            target_date_data[int(sid)] = {
                "B": row[self.csv_file_titles[1]],
            }
        
        # 更新 Excel 文件
        for row in range(1, sheet.max_row + 1):
            student_id = sheet.cell(row=row, column=1).value

            if student_id in yesterday_data:
                # 将前一天的数据平移到 D 列
                sheet.cell(row=row, column=4, value=yesterday_data[student_id]['B'])

            if student_id in target_date_data:
                # 更新当天的数据到 C 列
                sheet.cell(row=row, column=3, value=target_date_data[student_id]['B'])

        # 保存新的 Excel 文件，命名为当天的日期（mmdd格式）
        new_file_path = None
        try:
            wb.save(self.new_excel_file)
            new_file_path = self.new_excel_file
        except PermissionError:
            print(f"❌ 无法保存文件：{self.new_excel_file}\n可能是该文件正在被 Excel 打开，请关闭后重试。")
        
        wb.close()
        wb_vals.close()
        return new_file_path

class UpdaterFactory:
    def create_updater(self, updatername: str, target_date: datetime = None) -> Updater:
        if target_date is None:
            target_date = datetime.today()

        yesterday = target_date - timedelta(days=1)
        # 自动生成文件路径
        csv_file = os.path.join(SAVE_DIR, f"{updatername}_{target_date.strftime("%Y-%m-%d")}.csv")
        old_excel_file = os.path.join(SAVE_DIR, f"{updatername}_{yesterday.strftime("%m%d")}.xlsx")
        new_excel_file = os.path.join(SAVE_DIR, f"{updatername}_{target_date.strftime("%m%d")}.xlsx")
        
        # 打印检查信息
        print(f"📘 输入 CSV 文件: {csv_file}")
        print(f"📗 输入 Excel 文件: {old_excel_file}")
        print(f"📙 输出 Excel 文件: {new_excel_file}")

        if updatername == "英语群":
            return EnglishUpdater(updatername, csv_file, old_excel_file, new_excel_file)
        elif updatername == "数学群":        
            return MathsUpdater(updatername, csv_file, old_excel_file, new_excel_file)
        elif updatername == "语文群":
            return MathsUpdater(updatername, csv_file, old_excel_file, new_excel_file)
        else:
            raise ValueError("不认识的群名称")

def main():
    # 用户输入完整日期
    yesterday_date = datetime.today() - timedelta(days=1)
    date_str = input(f"请输入要生成的日期（格式 yyyy-mm-dd 或 yyyy/mm/dd，例如 {yesterday_date.strftime("%Y-%m-%d")}）：").strip()

    # 兼容不同日期格式
    date_formats = ["%Y-%m-%d", "%Y/%m/%d", "%Y%m%d"]
    target_date = None
    for fmt in date_formats:
        try:
            target_date = datetime.strptime(date_str, fmt)
            break
        except ValueError:
            continue

    if target_date is None:
        print("❌ 无法识别输入的日期格式，请输入类似 2025-10-27 或 20251027。")
        # target_date = datetime.today()
        target_date = yesterday_date

    factory = UpdaterFactory()
    for name in GROUP_NAME:
        resp = input(f"要处理{name}吗？(y/n，默认 y)：").strip().lower()
        if resp == "":
            resp = "y"   # 用户直接回车时，默认设为 y
        if resp != "y":
            continue

        updater = factory.create_updater(name, target_date)

        if not updater.ask_overwrite():
            continue

        # 调用你写好的核心函数
        try:
            updater.recalc_and_save_excel()
            new_excel_file = updater.update_excel_with_target_data()
            if not new_excel_file is None:
                print(f"✅ 已成功生成：{new_excel_file}")
        except PermissionError:
                print(f"❌ 无法保存 {new_excel_file}，请关闭该文件后重试。")
        except Exception as e:
                print(f"❌ 发生错误：{e}")
    
if __name__ == "__main__":
    main()